{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('2006-01-05', 'buy', 'hat', 100.0, 35.14)\n",
      "('2006-01-05', 'sell', 'pen', 90.0, 15.21)\n"
     ]
    }
   ],
   "source": [
    "#chap10b Python网络与数据库程序设计26\n",
    "import sqlite3\n",
    "conn=sqlite3.connect(\"../ds/example.db\")\n",
    "cur=conn.cursor()\n",
    "cur=conn.execute(\"create table stocks(data text,trans text,symbol text,qty real,price real)\")\n",
    "cur.execute(\"insert into stocks values('2006-01-05','buy','hat',100,35.14)\")\n",
    "cur.execute(\"insert into stocks values('2006-01-05','sell','pen',90,15.21)\")\n",
    "for row in conn.execute(\"select * from stocks\"):\n",
    "    print(row)\n",
    "\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 创建表并插入数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('James', 'male', 13888997011, 66735, 'NY')\n",
      "('Lily', 'female', 15808066055, 675797, 'LA')\n",
      "('Frank', 'male', 15912108090, 3232099, 'DC')\n"
     ]
    }
   ],
   "source": [
    "import sqlite3\n",
    "conn=sqlite3.connect(\"../ds/addressList.db\")\n",
    "cur=conn.cursor()\n",
    "\n",
    "cur=conn.execute(\"create table addressList(name text,sex text ,phon int,QQ int,address text)\")\n",
    "cur.execute(\"insert into addressList(name,sex,phon,QQ,address) values('James','male','13888997011','66735','NY' )\")\n",
    "cur.execute(\"insert into addressList(name,sex,phon,QQ,address) values('Lily','female','15808066055','675797','LA')\")\n",
    "cur.execute(\"insert into addressList(name,sex,phon,QQ,address) values('Frank','male','15912108090','3232099','DC')\")\n",
    "\n",
    "for row in conn.execute(\"select * from addressList\"):\n",
    "    print(row)\n",
    "\n",
    "conn.commit()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 查询数据"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "James\t\t\tmale\t\t\t13888997011\t\t\t66735\t\t\tNY\t\t\t\n",
      "Lily\t\t\tfemale\t\t\t15808066055\t\t\t675797\t\t\tLA\t\t\t\n",
      "Frank\t\t\tmale\t\t\t15912108090\t\t\t3232099\t\t\tDC\t\t\t\n"
     ]
    }
   ],
   "source": [
    "#读取数据\n",
    "import sqlite3\n",
    "\n",
    "conn = sqlite3.connect('../ds/addressList.db')\n",
    "cur = conn.cursor()\n",
    "cur.execute('select * from addressList') #RollCall\n",
    "li = cur.fetchall()\n",
    "\n",
    "for line in li:\n",
    "    for item in line:\n",
    "        if type(item) != str:\n",
    "            s = str(item)\n",
    "        else:\n",
    "            s=item\n",
    "        print(s+'\\t\\t\\t',end='')\n",
    "    print()\n",
    "\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# python连接Microsoft access数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(0.0, 1111.0, 1658991.0, 139.0) 1659130.0\n"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "# 功能:python连接access2010数据库(.accdb)\n",
    "# 这个在windows下 office中应用\n",
    "\n",
    "import pyodbc\n",
    "DBfile = r\"../ds/tank.accdb\"  # 数据库文件\n",
    "conn = pyodbc.connect(r\"Driver={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=\" + DBfile + \";Uid=;Pwd=;\")\n",
    "\n",
    "\n",
    "cursor = conn.cursor()\n",
    "SQL = \"SELECT * from `2101` where high = 1111.0;\"\n",
    "\n",
    "for row in cursor.execute(SQL):\n",
    "    # print(row)\n",
    "    print(row,row[2]+ row[3])\n",
    "\n",
    "cursor.close()\n",
    "conn.close()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# python连接Microsoft sql server数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'\\nimport sys\\nimport pymssql\\n#尝试数据库连接\\nconn = pymssql.connect(host=\"127.0.0.1\",user=\"sa\",password=\"\", database=\"master\")\\ncur = conn.cursor()\\n#查询数据库\\nquery=\"select name, database_id, recovery_model_desc from sys.databases\"\\ncur.execute(query)\\nconn.commit\\nrows = cur.fetchall()\\n\\nprint(\\' \\')\\nprint(\\'-----结果返回中------- \\')\\nprint(\\' \\')\\nfor (name,database_id,recovery_model_desc) in rows:\\n    database_id=bytes(database_id) #将int转换转换为字符串类型\\n    print(\\'数据库名:\\' +str(name)+\\';\\')\\n    print( \\'数据库id:\\' +database_id )\\n    print( \\'数据库恢复模式:\\'+str(recovery_model_desc) )\\n    print(\\' \\')\\n    print(\\'-----以上是所有结果!------- \\')\\n    #关闭连接，释放资源\\n\\ncur.close()\\nconn.close()\\n'"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "# 功能:python连接mssqlserver数据库\n",
    "#这个在windows下 office中应用\n",
    "#pip install mssql\n",
    "'''\n",
    "import sys\n",
    "import pymssql\n",
    "#尝试数据库连接\n",
    "conn = pymssql.connect(host=\"127.0.0.1\",user=\"sa\",password=\"\", database=\"master\")\n",
    "cur = conn.cursor()\n",
    "#查询数据库\n",
    "query=\"select name, database_id, recovery_model_desc from sys.databases\"\n",
    "cur.execute(query)\n",
    "conn.commit\n",
    "rows = cur.fetchall()\n",
    "\n",
    "print(' ')\n",
    "print('-----结果返回中------- ')\n",
    "print(' ')\n",
    "for (name,database_id,recovery_model_desc) in rows:\n",
    "    database_id=bytes(database_id) #将int转换转换为字符串类型\n",
    "    print('数据库名:' +str(name)+';')\n",
    "    print( '数据库id:' +database_id )\n",
    "    print( '数据库恢复模式:'+str(recovery_model_desc) )\n",
    "    print(' ')\n",
    "    print('-----以上是所有结果!------- ')\n",
    "    #关闭连接，释放资源\n",
    "\n",
    "cur.close()\n",
    "conn.close()\n",
    "'''"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# python连接mysql数据库"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "\"\\ncreate database test; \\nuse test;\\ncreate table students (\\n ID int primary key, \\n Name varchar(20), \\n Sex char(1), \\n Birthday varchar(10),\\n Age int\\n);\\ninsert into students values (1,'Tom','M','1990-1-2',18); \\ninsert into students values (2,'Jerry','F','1991-2-5',17); \\n\\nselect * from students;\\n\""
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#启动MySql服务命令为：net start mysql\n",
    "#关闭MySql服务命令为：net stop mysql\n",
    "# cd d:\\mysql5.6\\bin  \n",
    "# mysql -u root -p\n",
    "# 此处passwd 为空\n",
    "'''\n",
    "create database test; \n",
    "use test;\n",
    "create table students (\n",
    " ID int primary key, \n",
    " Name varchar(20), \n",
    " Sex char(1), \n",
    " Birthday varchar(10),\n",
    " Age int\n",
    ");\n",
    "insert into students values (1,'Tom','M','1990-1-2',18); \n",
    "insert into students values (2,'Jerry','F','1991-2-5',17); \n",
    "\n",
    "select * from students;\n",
    "'''\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1, 'Tom', 'M', '1990-1-2', 18)\n"
     ]
    }
   ],
   "source": [
    "# -*- coding: utf-8 -*-\n",
    "# 功能:python连接mysql数据库\n",
    "#pip install pymysql\n",
    "import pymysql\n",
    "conn = pymysql.connect(host='127.0.0.1', port=3306,user='root', passwd='root', db='mysql')\n",
    "cur = conn.cursor()\n",
    "cur.execute(\"USE test\")\n",
    "cur.execute(\"SELECT * FROM students\")\n",
    "print(cur.fetchone())\n",
    "\n",
    "cur.close()\n",
    "conn.close()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
